/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package at.tugraz.hidipic.service;

import at.tugraz.hidipic.model.*;
import at.tugraz.hidipic.web.KDDConstants;
import at.tugraz.hidipic.web.taglib.KDDTagLib;
import java.util.*;
import org.apache.log4j.Logger;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.support.DataAccessUtils;
import org.springframework.orm.hibernate3.HibernateTemplate;

/**
 *
 * @author mario
 */
public class DataAccessService {

    @Autowired
    HibernateTemplate hibernateTemplate;
    private final Logger LOG = Logger.getLogger(DataAccessService.class);

    /**
     * checks if the subproject has been shared to the user, if he is a
     * researcher or a leader of the parent (i.e. is allowed to see it)
     *
     * @param sp
     * @param u
     * @return true if yes, false if not
     */
    public boolean isSubProjectAvailableToUser(SubProject sp, User u) {
        try {
            Project parent = sp.getParent();
            if (KDDTagLib.hasRole(u, KDDConstants.ADMIN_ROLE)) {
                return true;
            }
            if (u.getUsername().equals(parent.getProjectLeader().getUsername())) {
                return true;
            }
            if (getResearcherSubProjectsForUser(u).contains(sp)) {
                return true;
            }
            if (getSharedSubProjectsForUser(u).contains(sp)) {
                return true;
            }
        } catch (Exception e) {
            LOG.error(e);
        }
        return false;
    }

    /**
     * checks if the project has subprojects that have been shared to the user,
     * if he is a researcher of one of them or a leader of the project (i.e. is
     * allowed to see it)
     *
     * @param p
     * @param u
     * @return true if yes, false if not
     */
    public boolean isProjectAvailableToUser(Project p, User u) {
        try {
            if (KDDTagLib.hasRole(u, KDDConstants.ADMIN_ROLE)) {
                return true;
            }
            if (p.getProjectLeader().getUsername().equals(u.getUsername())) {
                return true;
            }
            if (getResearcherSubProjectsForUserInProject(u, p).size() > 0) {
                return true;
            }
            if (getSharedSubProjectsForUserInProject(u, p).size() > 0) {
                return true;
            }
        } catch (Exception e) {
            LOG.error(e);
        }
        return false;
    }

    /**
     * get uploaded data of a user
     *
     * @param user
     * @return a list of the data
     */
    public List<DataItem> getCreatedDataForUser(User user) {
        return hibernateTemplate.find("from DataItem where creator = ?", user);
    }

    /**
     * get the projects a user is leading
     *
     * @param user
     * @return the list of projects
     */
    public List<Project> getLeadProjectsForUser(User user) {
        return hibernateTemplate.find("from Project where projectLeader = ?", user);
    }

    /**
     * gets all results a certain user has created
     *
     * @param user
     * @return
     */
    public List<Result> getCreatedResultsForUser(User user, Integer limit) {
        if (user != null) {
            List<Result> results;
            if (limit == null || limit <= 0) {
                results = hibernateTemplate.find("from Result where creator = ? ORDER BY creationDate desc", user);
            } else {
                results = hibernateTemplate.find("from Result where creator = ? ORDER BY creationDate desc", user);
                if (results != null && results.size() >= limit) {
                    return results.subList(0, limit);
                }
            }
            return results;
        }
        return Collections.EMPTY_LIST;
    }

    /**
     * get the comments of a subproject
     *
     * @param sp
     * @return list of comments
     */
    public List<Comment> getCommentsForSubProject(SubProject sp) {
        return hibernateTemplate.find("from Comment where subProject = ?", sp);
    }

    /**
     * get the comments of a result
     *
     * @param r
     * @return list of comments
     */
    public List<Comment> getCommentsForResult(Result r) {
        return hibernateTemplate.find("from Comment where result = ?", r);
    }

    /**
     * get the comments of a project
     *
     * @param p
     * @return list of comments
     */
    public List<Comment> getCommentsForProject(Project p) {
        return hibernateTemplate.find("from Comment where project = ?", p);
    }

    /**
     * get the output data for a result
     *
     * @param r
     * @return list of data
     */
    public List<OutputDataItem> getOutputDataForResult(Result r) {
        return hibernateTemplate.find("from OutputDataItem where result = ?", r);
    }

    /**
     * get all subprojects in a project
     *
     * @param p
     * @return list of subprojects
     */
    public Set<SubProject> getSubProjectsForProject(Project p) {
        List<SubProject> subProjects = hibernateTemplate.find("from SubProject where parent = ?", p);
        Set<SubProject> sp = new HashSet<SubProject>(subProjects);
        return sp;
    }

    /**
     * get results in a subproject
     *
     * @param sp
     * @return list of results
     */
    public List<Result> getResultsForSubProject(SubProject sp) {
        return hibernateTemplate.find("from Result where subProject = ?", sp);
    }

    /**
     * get all events for a subproject, limited by limit
     *
     * @param sp
     * @param limit
     * @return list of events
     */
    public List<Event> getEventsForSubProject(SubProject sp, Integer limit) {
        if (sp != null) {
            List<Event> events;
            if (limit == null || limit <= 0) {
                events = hibernateTemplate.find("from Event where subProject = ? ORDER by creationDate DESC", sp);
            } else {
                events = hibernateTemplate.find("from Event where subProject = ? ORDER by creationDate DESC", sp);
                if (events != null && events.size() >= limit) {
                    return events.subList(0, limit);
                }
            }
            return events;
        }
        return Collections.EMPTY_LIST;
    }

    /**
     * get all events for a user, limited by limit
     *
     * @param user
     * @param limit
     * @return list of events
     */
    public List<Event> getEventsForUser(User user, Integer limit) {
        if (user != null) {
            Session session = hibernateTemplate.getSessionFactory().openSession();
            try {
                SQLQuery query;
                if (limit == null) {
                    query = session.createSQLQuery("select * from event where idSubProject IN (select a.id from subproject as a join user_subproject b on b.followSubProjects_id = a.id join user as c on c.username = b.user_username where c.username = :usrname) ORDER BY creationDate DESC");
                } else {
                    query = session.createSQLQuery("select * from event where idSubProject IN (select a.id from subproject as a join user_subproject b on b.followSubProjects_id = a.id join user as c on c.username = b.user_username where c.username = :usrname) ORDER BY creationDate DESC LIMIT " + limit.toString());
                }
                query.setParameter("usrname", user.getUsername());
                query.addEntity(Event.class);
                List<Event> events = query.list();
                return events;
            } catch (Exception e) {
                LOG.error(e);
            } finally {
                session.close();
            }
        }
        return Collections.EMPTY_LIST;
    }

    /**
     * get all users with the given userrole
     *
     * @param userRole
     * @return list of users
     */
    public List<User> getAllUsersWithLevel(String userRole) {
        if (userRole != null) {
            Session session = hibernateTemplate.getSessionFactory().openSession();
            try {
                SQLQuery query = session.createSQLQuery("select * from user as a join user_customgrantedauthority as b on b.user_username = a.username join customgrantedauthority as c on b.authorities_authority = c.authority where c.authority = :authname");
                query.setParameter("authname", userRole);
                query.addEntity(User.class);
                List<User> results = query.list();
                return results;
            } catch (Exception e) {
                LOG.error(e);
            } finally {
                session.close();
            }
        }
        return Collections.EMPTY_LIST;
    }

    /**
     * get all subprojects a user is a researcher at
     *
     * @param user
     * @return list of subprojects
     */
    public List<SubProject> getResearcherSubProjectsForUser(User user) {
        if (user != null) {
            Session session = hibernateTemplate.getSessionFactory().openSession();
            try {
                SQLQuery query = session.createSQLQuery("select * from subproject as a join researchersinsubproject b on b.subproject_id = a.id join user as c on c.username = b.researchers_username where c.username = :usrname");
                query.setParameter("usrname", user.getUsername());
                query.addEntity(SubProject.class);
                List<SubProject> results = query.list();
                return results;
            } catch (Exception e) {
                LOG.error(e);
            } finally {
                session.close();
            }
        }
        return Collections.EMPTY_LIST;
    }

    /**
     * get all subprojects a user is a researcher in, filtered by a project
     *
     * @param user
     * @param project
     * @return list of subprojects
     */
    public List<SubProject> getResearcherSubProjectsForUserInProject(User user, Project project) {
        if (user != null && project != null) {
            Session session = hibernateTemplate.getSessionFactory().openSession();
            try {
                SQLQuery query = session.createSQLQuery("select * from subproject as a join researchersinsubproject b on b.subproject_id = a.id join user as c on c.username = b.researchers_username where c.username = :usrname and a.idProject = :projid");
                query.setParameter("usrname", user.getUsername());
                query.setParameter("projid", project.getId());
                query.addEntity(SubProject.class);
                List<SubProject> results = query.list();
                return results;
            } catch (Exception e) {
                LOG.error(e);
            } finally {
                session.close();
            }
        }
        return Collections.EMPTY_LIST;
    }

    /**
     * make sure every user is always a USER_ROLE in addition to it's other
     * roles
     *
     * @param auths
     * @return set of authorities to set
     */
    public Set<CustomGrantedAuthority> handleAuthorities(Set<CustomGrantedAuthority> auths) {
        if (auths != null) {
            if (!auths.contains(KDDConstants.USER_ROLE)) {
                auths.add(hibernateTemplate.get(CustomGrantedAuthority.class, KDDConstants.USER_ROLE));
            }
        } else {
            auths = new HashSet<CustomGrantedAuthority>();
            auths.add(hibernateTemplate.get(CustomGrantedAuthority.class, KDDConstants.USER_ROLE));
        }
        return auths;
    }

    /**
     * method to collect statistics for the frontpage
     *
     * @return map with statistics
     */
    public Map<String, Integer> getStatistics() {
        Map<String, Integer> stats = new HashMap<String, Integer>();
        Integer projects = DataAccessUtils.intResult(hibernateTemplate.find("select count(*) from Project"));
        Integer methods = DataAccessUtils.intResult(hibernateTemplate.find("select count(*) from Method"));
        Integer users = DataAccessUtils.intResult(hibernateTemplate.find("select count(*) from User"));
        Integer subprojects = DataAccessUtils.intResult(hibernateTemplate.find("select count(*) from SubProject"));
        Integer results = DataAccessUtils.intResult(hibernateTemplate.find("select count(*) from Result"));
        Integer events = DataAccessUtils.intResult(hibernateTemplate.find("select count(*) from Event"));
        Integer data = DataAccessUtils.intResult(hibernateTemplate.find("select count(*) from InputDataItem"));
        Integer comments = DataAccessUtils.intResult(hibernateTemplate.find("select count(*) from Comment"));
        stats.put("projects", projects);
        stats.put("methods", methods);
        stats.put("users", users);
        stats.put("subprojects", subprojects);
        stats.put("results", results);
        stats.put("events", events);
        stats.put("data", data);
        stats.put("comments", comments);
        return stats;
    }

    /**
     * get all shared subprojects for a user
     *
     * @param user
     * @return list of subprojects
     */
    public List<SubProject> getSharedSubProjectsForUser(User user) {
        if (user != null) {
            Session session = hibernateTemplate.getSessionFactory().openSession();
            try {
                SQLQuery query = session.createSQLQuery("select * from subproject as a join shareduserlists b on b.subproject_id = a.id join user as c on c.username = b.shareduserlist_username where c.username = :usrname");
                query.setParameter("usrname", user.getUsername());
                query.addEntity(SubProject.class);
                List<SubProject> results = query.list();
                return results;
            } catch (Exception e) {
                LOG.error(e);
            } finally {
                session.close();
            }
        }
        return Collections.EMPTY_LIST;
    }

    /**
     * get all shared subprojects for a user in a project
     *
     * @param user
     * @return list of subprojects
     */
    public List<SubProject> getSharedSubProjectsForUserInProject(User user, Project project) {
        if (user != null && project != null) {
            Session session = hibernateTemplate.getSessionFactory().openSession();
            try {
                SQLQuery query = session.createSQLQuery("select * from subproject as a join shareduserlists b on b.subproject_id = a.id join user as c on c.username = b.shareduserlist_username where c.username = :usrname and a.idProject = :projid");
                query.setParameter("usrname", user.getUsername());
                query.setParameter("projid", project.getId());
                query.addEntity(SubProject.class);
                List<SubProject> results = query.list();
                return results;
            } catch (Exception e) {
                LOG.error(e);
            } finally {
                session.close();
            }
        }
        return Collections.EMPTY_LIST;
    }

    /**
     * get all subprojects a user is following
     *
     * @param user
     * @return list of subprojects
     */
    public List<SubProject> getFollowedSubProjectsForUser(User user) {
        if (user != null) {
            Session session = hibernateTemplate.getSessionFactory().openSession();
            try {
                SQLQuery query = session.createSQLQuery("select * from subproject as a join user_subproject b on b.followSubProjects_id = a.id join user as c on c.username = b.user_username where c.username = :usrname");
                query.setParameter("usrname", user.getUsername());
                query.addEntity(SubProject.class);
                List<SubProject> results = query.list();
                return results;
            } catch (Exception e) {
                LOG.error(e);
            } finally {
                session.close();
            }
        }
        return Collections.EMPTY_LIST;
    }

    /**
     * get all subprojects a user is following inside a certain project
     *
     * @param user
     * @param project
     * @return list of subprojects
     */
    public List<SubProject> getFollowedSubProjectsForUserInProject(User user, Project project) {
        if (user != null) {
            Session session = hibernateTemplate.getSessionFactory().openSession();
            try {
                SQLQuery query = session.createSQLQuery("select * from subproject as a join user_subproject b on b.followSubProjects_id = a.id join user as c on c.username = b.user_username where c.username = :usrname AND a.idProject = :proj");
                query.setParameter("usrname", user.getUsername());
                query.setParameter("proj", project.getId());
                query.addEntity(SubProject.class);
                List<SubProject> results = query.list();
                return results;
            } catch (Exception e) {
                LOG.error(e);
            } finally {
                session.close();
            }
        }
        return Collections.EMPTY_LIST;
    }

    /**
     * make sure old values are also saved at an edit
     *
     * @param project
     * @param updateProject
     * @throws Exception
     */
    public void updateProjectFromEdit(Project project, Project updateProject) throws Exception {
        if (project != null && updateProject != null) {
            project.setId(updateProject.getId());
            project.setCreationDate(updateProject.getCreationDate());
            project.setCreator(updateProject.getCreator());
            project.setEvents(updateProject.getEvents());
            project.setSubProjects(updateProject.getSubProjects());
            project.setComments(updateProject.getComments());
        } else {
            throw new Exception("Project was null.");
        }
    }

    /**
     * make sure old values are also saved at an edit
     *
     * @param project
     * @param updateProject
     * @throws Exception
     */
    public void updateSubProjectFromEdit(SubProject project, SubProject updateProject) throws Exception {
        if (project != null && updateProject != null) {
            project.setId(updateProject.getId());
            project.setCreationDate(updateProject.getCreationDate());
            project.setCreator(updateProject.getCreator());
            project.setEvents(updateProject.getEvents());
            project.setResults(updateProject.getResults());
            project.setComments(updateProject.getComments());
            project.setSharedUserList(updateProject.getSharedUserList());
        } else {
            throw new Exception("SubProject was null.");
        }
    }
}
